package org.compiere.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class Mhrmillnessline extends X_hrm_illness_line {

	/**
	 * 
	 */
	private static final long serialVersionUID = 8107794591984234259L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmillnessline.class);

	public Mhrmillnessline(Properties ctx, int hrm_illness_line_ID,
			String trxName) {
		super(ctx, hrm_illness_line_ID, trxName);
		// TODO Auto-generated constructor stub
	}

	public Mhrmillnessline(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
		// TODO Auto-generated constructor stub
	}
	
public static Mhrmillnessline getIllnessRec(int C_BPartner_ID, Timestamp dt) {
		
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmillnessline hrmill = null;
			
		// 
			String sql_ = "SELECT * FROM HRM_ILLNESS_LINE \n " +
							"WHERE ISACTIVE='Y' \n " +
							"AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
							"AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
							"AND STARTDATE BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
							"AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";														
			try
			{
				pstmt = DB.prepareStatement(sql_,null);				
				rs = pstmt.executeQuery();	
				if(rs.next())
					hrmill = new Mhrmillnessline(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
		return hrmill;
		
	}

public static Mhrmillnessline getIllness(int hrm_illness_id, boolean type) {
	
	if (hrm_illness_id == 0)
		return null;
	String stype = "";
	
	if (type == true)
		stype= "Y";
	else
		stype = "N";
	
	//
    PreparedStatement pstmt = null;
	ResultSet rs = null;
	Mhrmillnessline result = null;
		
	// 
		String sql_ = "SELECT * FROM HRM_ILLNESS_LINE \n " +
						"WHERE ISACTIVE='" + stype + "' \n " +
						"AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						"AND HRM_ILLNESS_ID=" + hrm_illness_id ;							
		try
		{
			pstmt = DB.prepareStatement(sql_,null);				
			rs = pstmt.executeQuery();	
			if(rs.next())
				result = new Mhrmillnessline(Env.getCtx(), rs, null);
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
	return result;	
 }

public static Mhrmillnessline[] getIllLineArray(int C_BPartner_ID, Timestamp dt) {
	
	if (C_BPartner_ID == 0)
		return null;
	
	//
    PreparedStatement pstmt = null;
	ResultSet rs = null;
	Mhrmillnessline result = null;
	
	ArrayList<Mhrmillnessline> list = new ArrayList<Mhrmillnessline>();
		
	// 
		String sql_ = "SELECT * FROM HRM_ILLNESS_LINE \n " +
						" WHERE ISACTIVE='Y' \n " +
						" AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
						" AND STARTDATE BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
						" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";														
		try
		{
			pstmt = DB.prepareStatement(sql_,null);				
			rs = pstmt.executeQuery();	
			while(rs.next()){
				result = new Mhrmillnessline(Env.getCtx(), rs, null);
				list.add(result);
			}				
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		return list.toArray(new Mhrmillnessline[list.size()]);	
	}

	public static int getillDay(int C_BPartner_ID, Timestamp dt, String atype) {
		
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		int result = 0;
		
		String sql = " SELECT COUNT(*) FROM HRM_SCHEDULESETTINGS_V \n" +
						 " WHERE AD_CLIENT_ID ="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						 " AND C_BPARTNER_ID = " + C_BPartner_ID + " \n " +
						 " AND ABSENCE_ID IS NOT NULL \n " +
						 " AND HOLIDAYS_ID IS NULL \n " +
						 " AND WORKSCHEDULE_ONEDAYTYPE_ID=1000001 \n " +
						 " AND ABSENCETYPE='" + atype + "' \n " +
						 " AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
						 " AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
		try
		{
			pstmt = DB.prepareStatement(sql,null);				
			rs = pstmt.executeQuery();	
			if(rs.next())
				result = rs.getInt(1);
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}			
		
		return result;	
	}
	
	public static Timestamp getMaxDate(int C_BPartner_ID, Timestamp dt, String atype) {
		
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Timestamp result = null;
		
		String sql = " SELECT MAX(DATEFROM) AS MAX FROM HRM_SCHEDULESETTINGS_V \n" +
						 " WHERE AD_CLIENT_ID ="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						 " AND C_BPARTNER_ID = " + C_BPartner_ID + " \n " +
						 " AND ABSENCE_ID IS NOT NULL \n " +
						 " AND HOLIDAYS_ID IS NULL \n " +						
						 " AND ABSENCETYPE='" + atype + "' \n " +
						 " AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
						 " AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
		try
		{
			pstmt = DB.prepareStatement(sql,null);				
			rs = pstmt.executeQuery();	
			if(rs.next())
				result = rs.getTimestamp(1);
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}			
		
		return result;	
	}
	
	public static Timestamp getMinDate(int C_BPartner_ID, Timestamp dt, String atype) {
		
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Timestamp result = null;
		
		String sql = " SELECT MIN(DATEFROM) AS MIN FROM HRM_SCHEDULESETTINGS_V \n" +
						 " WHERE AD_CLIENT_ID ="  + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
						 " AND C_BPARTNER_ID = " + C_BPartner_ID + " \n " +
						 " AND ABSENCE_ID IS NOT NULL \n " +
						 " AND HOLIDAYS_ID IS NULL \n " +						
						 " AND ABSENCETYPE='" + atype + "' \n " +
						 " AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
						 " AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
		try
		{
			pstmt = DB.prepareStatement(sql,null);				
			rs = pstmt.executeQuery();	
			if(rs.next())
				result = rs.getTimestamp(1);
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}			
		
		return result;	
	}

}